package cn.tsingyu.antsclub.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;

import cn.tsingyu.antsclub.bean.Trade;
import cn.tsingyu.antsclub.util.ExcelUtil;
import jxl.write.WritableWorkbook;
/**
 * 
 * @author 土龙 516928192@qq.com
 * AntsClub_社群管理系统
 * 2016年12月21日
 */
@Service
public class TradeService{

    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 获取在线充值列表
     * @param startTime
     * @param endTime
     * @return 
     */
	public List<Trade> getRecord(String startTime,String endTime) {
		final List<Trade> list = new ArrayList<Trade>();
		String sql= "SELECT * FROM trade WHERE issue=? "
		+ " and createTime >= ? and createTime<? order by id ";
		jdbcTemplate.query(sql,new Object[] {"在线充值",startTime,endTime},
			new RowCallbackHandler() {  
	            public void processRow(ResultSet rs) throws SQLException {
	            	Trade trade = new Trade();
	            	trade.setIssue(rs.getString("issue"));
	            	trade.setTradeamount(rs.getString("tradeamount")); 
	            	trade.setSourcename(rs.getString("sourceid")+"_"+rs.getString("sourcename"));
	            	trade.setRemark(rs.getString("remark"));
	            	trade.setCreatetime(rs.getTimestamp("createtime").toString().substring(0, 19));
	            	trade.setCreateusername(rs.getString("createusername"));
	            	list.add(trade);
	            }  
	        }
	    );
		return list;
	}
	public String[][]  getRecordArray(String startTime,String endTime) {
		List<Trade> list = getRecord(startTime,endTime);
		int listSize = list.size();
		String[][] data = new String[listSize+2][6];
		Double sum=0D;
		for (int i = 0; i < listSize; i++) {
			Trade t = list.get(i);
			data[i][0]=t.getIssue();
			data[i][1]=t.getTradeamount();
			data[i][2]=t.getSourcename();
			data[i][3]=t.getRemark();
			data[i][4]=t.getCreatetime().toString();
			data[i][5]=t.getCreateusername();
			sum+=Double.parseDouble(t.getTradeamount());
		}
		NumberFormat nFormat=NumberFormat.getNumberInstance(); 
	    nFormat.setMaximumFractionDigits(2);//设置小数点后面位数为
	    nFormat.format(3.1415);
		String fee = nFormat.format(sum*0.0055);
		String pay = nFormat.format(sum-Double.parseDouble(fee));
		
		String[] empty = { "", "", "", "", "", ""};
		data[listSize] = empty;
		String[] footer = { "充值总计", sum+"", "手续费", fee, "应转", pay};
		data[listSize+1] = footer;
		return data;
	}
	
	public void exportRechargeRecord(WritableWorkbook book,String excelName,String startTime,String endTime){
		ExcelUtil ce = new ExcelUtil();
		String[] header = { "交易类型", "交易金额", "付款人", "备注", "操作时间", "操作人"};
		String[][] data =getRecordArray(startTime,endTime);
		String[] footer = { "", "", "", "", "", ""};
		ce.exportDailyPlanTemplate(book,excelName,header,data,footer);
	}
}
